<?php

namespace Models;

class Stredisko extends Base
{
	/**
	 * If user doesn't exist, create it. Otherwise update it.
	 * @return false on update, true on new user
	 */

        public function getStrediskoKronika()
        {
		return $this->db->query("
                    SELECT 1 as id, GROUP_CONCAT('<li><a href=\"./kronika.php#',(kr.id + 1),'\">',kr.akce,' (',od.oddil,')</a> ' SEPARATOR ' ') as vKron
                    FROM(
                        SELECT * FROM pov_kronika2 ORDER BY datum_od DESC LIMIT 6) as kr
                    LEFT JOIN pov_oddily od ON(kr.oddil=od.id)
                                        ");

        }
        public function getStrediskoForum()
        {

		return $this->db->query("
                    SELECT 1 as id,GROUP_CONCAT('<a href=\"./forum/\">',fo.text,'</a><span class=\"krkdo\">přidal/a:',fo.od,'</span> 'SEPARATOR ' ') as vFor
                    FROM (SELECT * FROM pov_forum ORDER BY id DESC LIMIT 1) as fo
                                        ");

        }
        public function getStrediskoAktuality()
        {

		return $this->db->query("
                    SELECT 
                        1 as id,
                        GROUP_CONCAT('<li><a href=\"./aktuality.php#',(a.id + 1),'\">',a.nadpis,' (',od.oddil,')</a>' ORDER BY a.id DESC SEPARATOR ' ')as vAktu
                    FROM (SELECT * FROM pov_aktuality ORDER BY id desc LIMIT 5) as a
                    LEFT JOIN pov_oddily od ON(a.oddil=od.id)
                                        ");

        }
        public function getKronika()
        {

		return $this->db->query("
                    SELECT  kron.datum_od as od,
                            kron.datum_do as do,
                            kron.akce as co,
                            kron.zapis as text,
                            kron.id as cislo,
                            odd.oddil as kdo,
                            sodd.oddil as skym,
                            clen.nick as zapsal

                    FROM pov_kronika2 kron
                        JOIN pov_clenove clen ON(clen.id=kron.clen)
                        LEFT JOIN pov_oddily odd ON(odd.id=kron.oddil)
                        LEFT JOIN pov_oddily sodd ON(sodd.id=kron.soddil)
                    ORDER BY od DESC
                                        ");

        }
        public function getAktuality()
        {

		return $this->db->query("
                    SELECT  aktu.id as cislo,
                            aktu.nadpis as co,
                            aktu.text as text,
                            odd.oddil as kdo,
                            aktu.datum_od as od,
                            aktu.datum_do as do,
                            clen.nick as zapsal

                    FROM pov_aktuality aktu
                        JOIN pov_clenove clen ON(clen.id=aktu.clen)
                        LEFT JOIN pov_oddily odd ON(odd.id=aktu.oddil)
                    --  ORDER BY od DESC
                        ORDER BY aktu.id DESC
                                        ");

        }
        /*public function getOddily($oddil,$oddilName)
        {
                //$url0=bbasename(getcwd());
                //$url=$url0."/images/frcky/";
                $url="http://localhost/povoden2/www/images/frcky/";
                $this->db->query("SET group_concat_max_len = 2000");
		return $this->db->query("
                    SELECT  p.nick,
                            p.jmeno,
                            p.celek,
                            p.foto,
                            oddil.oddil
                            GROUP_CONCAT('<img class=\"clenfrcka\" src=\"$url',fr_im.img ,'\" alt=\"',fr_im.nazev ,'\"> 'ORDER BY fr_im.id ASC SEPARATOR ' ') as frcky
                    FROM
			(SELECT cl.nick,
                                cl.id,
                                cl.fce,
                                CONCAT('<b>Jméno: </b>', cl.jmeno, ' ', cl.prijmeni, '<br> ') as jmeno,
                                GROUP_CONCAT('<b>', otazky.otazka, ': </b>',odpovedi.odpoved, ' ' ORDER BY odpovedi.id ASC SEPARATOR ' <br> ')  as celek,
                                cl.foto

                         FROM pov_clenove cl

			 LEFT JOIN pov_otazky_".$oddilName." odpovedi ON(cl.id=odpovedi.clen)
			 LEFT JOIN pov_otazky_".$oddilName."_otazky otazky ON(otazky.id=odpovedi.otazka)

                         WHERE cl.oddil = '".$oddil."' AND cl.nick != 'Merf' AND cl.aktivni = '1'

                         GROUP BY cl.nick,cl.id
                            -- ORDER BY cl.fce DESC
			) as p

                     LEFT JOIN pov_fce fr ON(p.id=fr.clen)
                     LEFT JOIN pov_fce_frcky fr_im ON(fr_im.id=fr.frcka)
                     LEFT JOIN pov_oddily oddil ON(oddil.id=".$oddil.")

                     GROUP BY p.nick
                     ORDER BY p.fce DESC
                                        ");

        }*/
        public function getOddilyAktuality($oddil)
        {

		return $this->db->query("
                    SELECT * FROM pov_aktuality WHERE oddil = '$oddil' ORDER BY datum DESC LIMIT 3
                                        ");


        }
        public function update($name, $email, $username, $uid, $suppbuilding, $room)
	{
		$novy = false;


		$data = array('name'=>$name, 'email'=>$email, 'username'=> $username, 'shid'=> $uid);
		$result = $this->db->query("SELECT iduser, name, email, username, idbuilding FROM user WHERE shid = %i", $uid);

		if($result->getRowCount() < 1)
		{
			$this->db->query("INSERT INTO user", $data);
			$novy = true;
			if($this->db->affectedRows() != 1)
			{
				\Nette\Diagnostics\Debugger::log("Wrong affected rows on insert of uid: ".$uid);
				return False;
			}
		}
		else
		{
			$this->db->query("UPDATE user SET %a", $data, " WHERE iduser = %i", $result->fetch()->iduser);
		}

		$idbuilding = $this->db->query("SELECT idbuilding FROM building WHERE suppid = %s", $suppbuilding)->fetchSingle();
		if($idbuilding === false)
		{
			\Nette\Diagnostics\Debugger::log("Building is not in database id: ".$suppbuilding);
		}
		else
			$this->db->query("UPDATE user SET idbuilding = %i, roomno = %sN WHERE shid = %i", $idbuilding, $room, $uid);


		return $novy;
	}
	public function findIdFromUsername($username)
	{
		$res = $this->db->query("SELECT iduser FROM user WHERE username = %s", $username);
		if($res->count() == 1)
			return $res->fetchSingle();
		if($res->count() == 0)
			return -1;
		return -2;
	}
	public function isAdmin($id)
	{
		return $this->db->query("SELECT admin from user WHERE iduser = ", $id)->fetchSingle();
	}
	public function getInfo($id)
	{
		return $this->db->query("SELECT iduser, shid, roomno, email, user.name, username, admin, building.no as bno FROM user
			LEFT JOIN building ON user.idbuilding = building.idbuilding WHERE iduser = %i", $id)->fetch();

	}
	public function getBuildingAdmin($id)
	{
		if($this->isAdmin($id))
		{
			return $this->db->dataSource("SELECT idbuilding, name FROM building");
		}
		return $this->db->dataSource(
				"SELECT custodian.idbuilding, name FROM custodian INNER JOIN building ON custodian.idbuilding = building.idbuilding WHERE iduser = ", $id);

	}
	public function getRoomAdmin($id)
	{
		if($this->isAdmin($id))
		{
			return $this->db->dataSource("SELECT room.idroom, room.name, building.name as bname, room.no, building.no as bno FROM room JOIN
				building ON room.idbuilding = building.idbuilding");
		}
		return $this->db->dataSource(
				"SELECT room.idroom, room.name, building.name as bname, room.no, building.no as bno FROM custodian JOIN room ON custodian.idroom = room.idroom
				LEFT JOIN building ON room.idbuilding = building.idbuilding WHERE iduser = %i", $id,
				"UNION SELECT room.idroom, room.name, building.name as bname, room.no, building.no as bno FROM custodian
					JOIN building ON custodian.idbuilding = building.idbuilding JOIN room ON building.idbuilding = room.idbuilding WHERE custodian.iduser = %i", $id
				);
	}
	public function setPassword($pswd, $iduser)
	{
		$pswd = hash("sha256", $pswd);
		$res = $this->db->query("UPDATE user SET password=%s", $pswd, "WHERE iduser =%i", $iduser);
	}
	public function addOdkaz($url,$text,$popis= null,$sekce= 100)
        {   
            return $this->db->query("
                    INSERT INTO pov_odkazy (url, text, popis, sekce) VALUES (%s, %s, %s, %i)", $url,$text,$popis,$sekce
                                    );

        }
	public function addOdkazySekce($sekce)
        {
            return $this->db->query("
                    INSERT INTO pov_odkazy_sekce (sekce) VALUES (%s)",$sekce
                                    );

        }
	public function getOdkazySekce()
        {
            return $this->db->query("
                SELECT id_sekce, nazev
                FROM pov_odkazy_sekce
                                    ");

        }
        public function getOdkazy()
        {
            return $this->db->dataSource("
                SELECT *
                FROM pov_odkazy o
                LEFT JOIN pov_odkazy_sekce s ON(o.sekce=s.id_sekce)
                ORDER BY o.id DESC
                                    ");

        }

}

